import Config
import pymysql

TABLE_NEWS = "news"
TABLE_GOODS = "goods"
TABLE_STOCKS = "stock_a"


class SqlUtils:
    _conn = None
    _cursor = None

    def __init__(self,
                 db_host=Config.host,
                 db_user=Config.user,
                 db_password=Config.passwd,
                 db_name=Config.db,
                 db_port=Config.port):
        try:
            self._conn = pymysql.connect(host=db_host,
                                         user=db_user,
                                         password=db_password,
                                         database=db_name,
                                         port=db_port)
            self._cursor = self._conn.cursor()
            if self._conn is None or self._cursor is None:
                raise Exception('Error while connect to database.')
        except Exception as e:
            raise e

    def __del__(self):
        if self._cursor is not None:
            self._cursor.close()
            self._cursor = None
        if self._conn is not None:
            self._conn.close()
            self._conn = None

    def _getConnection(self):
        if self._conn is not None:
            return self._conn

    def _getCursor(self):
        if self._cursor is not None:
            return self._cursor

    def _deal_values(self, value):
        if isinstance(value, str):
            value = ("'{value}'".format(value=value))
        elif isinstance(value, dict):
            result = []
            for key, value in value.items():
                value = self._deal_values(value)
                res = "{key}={value}".format(key=key, value=value)
                result.append(res)
            return result
        else:
            value = (str(value))
        return value

    def execute(self, sql_cmd):
        try:
            self._getCursor().execute(sql_cmd)
            self._conn.commit()
            cursor = self._getCursor()
            return cursor
        except Exception:
            self._conn.rollback()
            return None

    def executeSerach(self, sql_cmd):
        try:
            self._getCursor().execute(sql_cmd)
            self._conn.commit()
            cursor = self._getCursor()
            jsonData = sql_fetch_json(cursor)
            return jsonData
        except Exception:
            self._conn.rollback()
            return None

    def insert(self, table, data):
        sql = ''
        key = ','.join(data.keys())
        values = map(self._deal_values, data.values())
        ins_data = ', '.join(values)
        sql = "INSERT INTO {table}({key}) VALUES ({val})".format(table=table,
                                                                 key=key,
                                                                 val=ins_data)
        return self.execute(sql)

    # def insert(self, table, ins_data):
    #     sql = ''
    #     for data in ins_data:
    #         key = ','.join(data.keys())
    #         values = map(self._deal_values, data.values())
    #         ins_data = ', '.join(values)
    #         sql = "INSERT INTO {table}({key}) VALUES ({val})".format(
    #             table=table, key=key, val=ins_data)
    #     return self.execute(sql)

    def update(self, table, data, condition=None):
        update_list = self._deal_values(data)
        update_data = ",".join(update_list)
        if condition is not None:
            condition_list = self._deal_values(condition)
            condition_data = ' AND '.join(condition_list)
            sql = "UPDATE {table} SET {values} WHERE {condition}".format(
                table=table, values=update_data, condition=condition_data)
        else:
            sql = "UPDATE {table} SET {values}".format(table=table,
                                                       values=update_data)
        return self.execute(sql)

    def select(self, table, filed, value):
        sql = "SELECT * FROM {table} WHERE {filed} = '{value}'".format(
            table=table, filed=filed, value=value)
        return self.execute(sql)

    def select_all(self, table):
        sql = "SELECT * FROM {table}".format(table=table)
        return self.execute(sql)

    def delete(self, table, condition={}):
        condition_list = self._deal_values(condition)
        condition_data = ' AND '.join(condition_list)
        sql = "DELETE FROM {table} {where} {condition}".format(
            table=table,
            where='' if condition_data == '' else 'WHERE',
            condition=condition_data)
        return self.execute(sql)


# 查询结果转json
def sql_fetch_json(cursor: pymysql.cursors.Cursor):
    """
    Convert the pymysql SELECT result to json format
    :param cursor:
    :return:
    """
    keys = []
    for column in cursor.description:
        keys.append(column[0])
    key_number = len(keys)

    json_data = []
    for row in cursor.fetchall():
        item = dict()
        for q in range(key_number):
            item[keys[q]] = row[q]
        json_data.append(item)

    return json_data